package back.dao.impl;

import back.dao.BackDao;
import common.date.BaseDateUitls;
import common.jdbc.BaseDBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import pojo.*;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 马泽朋
 * @version 1.0
 * @date 2019/10/20 11:25
 */
//SELECT * FROM `management` WHERE 1=1

public class BackDaoImpl implements BackDao {
    QueryRunner queryRunner = new QueryRunner(BaseDBUtils.getDataSource());


    /**
     * 根据账号密码登录后台管理界面
     *
     * @return
     */
    @Override
    public Management loginbackgroundByaccountAndpassword(Map<String,Object> map) throws SQLException {
        StringBuffer sql = new StringBuffer(" SELECT `id`,`account`,`password`,`nickname`," +
                "`grade`,`cratTime`,`ip`,`status`,`mailbox` FROM `management` WHERE 1=1 ");
        sql.append(" AND `account` = ? ");
        sql.append(" AND `password` = ? ");
       Object[] params = {map.get("username"),map.get("password")};
        Management query = queryRunner.query(sql.toString(),
                params, new BeanHandler<>(Management.class));
        return query;

    }

    /**
     * 采集单本小说入库，放入数据库
     *
     * @param map
     * @return
     */
    @Override
    public Map<String, Object> collectionOfSingleNovelLibrary(Map<String, Object> map) throws SQLException {



        //存放采集状态
        Map<String, Object> map1 = new HashMap<>();
        HashMap<String, Object> map2 = new HashMap<>(map);
        Set<Map.Entry<String, Object>> entries = map2.entrySet();
        //循环入库
        for (Map.Entry<String, Object> entry : entries) {
            //拿到连接
            Connection connection = BaseDBUtils.getConnection();
            //INSERT INTO gather (SELECT '06751d01357542fb97492d013beb2be4','/xiaoshuo/128/128396/'
            // WHERE NOT EXISTS (SELECT * FROM gather WHERE Collection = '/xiaoshuo/128/128396/'))
            //sql语句
            StringBuffer sql = new StringBuffer("INSERT INTO gather (SELECT ?,? " +
                    "WHERE NOT EXISTS (SELECT * FROM gather WHERE Collection = ?))");

            //预编译sql语句
            PreparedStatement preparedStatement = BaseDBUtils.getPreparedStatement(connection, sql.toString());

            //替换sql参数
            String[] prems = {entry.getKey(), (String)entry.getValue(),(String)entry.getValue()};
            //返回状态 大于0成功
            int i = BaseDBUtils.executeUpdate(preparedStatement, prems);
            if(i>0){
                map.put((String)entry.getValue(),"成功");
            }else if (i==0){
                map.put((String)entry.getValue(),"已存在");
            }else {
                map.put((String)entry.getValue(),"失败");
            }
        }
        return map1;
    }

    /**
     * 从数据库读取小说地址
     *
     * @param index 传入开始遍历的地址 index
     * @return 采集单本小说的对象包含贯彻全局的唯一uuid，和小说的url地址
     */
    @Override
    public List<CollectSingle> collectTheBasicInformationOfTheNovel(int index) throws SQLException {
        //存放采集状态
        Map<String, Object> map1 = new HashMap<>();
        //预编译sql语句
        StringBuffer sql = new StringBuffer("SELECT uuid,Collection FROM gather LIMIT ?,1000");
        //查询从指定结果开始的1000条记录
        List<CollectSingle> collectSingles = queryRunner.query(sql.toString(),index, new BeanListHandler<CollectSingle>(CollectSingle.class));
        return collectSingles;
    }

    /**
     * 采集单本小说信息入库
     *
     * @param list 小说实体集合
     * @return 包含运行结果
     * @throws SQLException sql异常
     */
    @Override
    public Map<String, Object> collectingNovelInformationIntoTheLibrary(List<Book> list) throws SQLException {
        StringBuffer sql = new StringBuffer("INSERT INTO books (SELECT ?,?,?,?,?,?,?,? " +
                " WHERE NOT EXISTS ( SELECT uuid FROM books WHERE uuid = ? ))");
        //拿到连接
        Connection connection = BaseDBUtils.getConnection();
        //预编译sql语句
        PreparedStatement preparedStatement = BaseDBUtils.getPreparedStatement(connection, sql.toString());
        //存放采集状态
        Map<String, Object> map = new HashMap<>();
        //存放替换参数
        Object[] parameter=null;

        for (Book book : list) {
            //拿到所有需要插入的参数
            parameter = new Object[]{book.getUuid(), book.getName(), book.getAuthor(), book.getState()
                    , book.getClassification(), book.getCount(), book.getCover(), book.getIntro(), book.getUuid()};
            //返回一个sql的执行状态，是否插入成功
            int i = BaseDBUtils.executeUpdate(preparedStatement,parameter);

            if(i>0){
                map.put(book.getName(),"成功");
            } else if (i==0){
                map.put(book.getName(),"已存在");
            }else {
                map.put(book.getName(),"失败");
            }
        }
        return map;
    }

    /**
     * 查询有的分类
     *
     * @param map 查询参数
     * @return 查询结果
     */
    @Override
    public List<Classification> queryClassification(Map<String, Object> map) throws SQLException {
        StringBuffer sql = new StringBuffer("SELECT categoryId,categoryName FROM `category`");
        List<Classification> list = queryRunner.query(sql.toString(), new BeanListHandler<Classification>(Classification.class));
        return list;
    }

    /**
     * 采集章节信息入库
     *
     * @param list 章节实体类
     * @return 存储结果
     */
    @Override
    public Map<String, Object> collectionChapter(List<Chapter> list) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        StringBuffer sql = new StringBuffer("INSERT INTO acquisitionsection " +
                "(SELECT ?,?,? WHERE NOT EXISTS " +
                "(SELECT uuid FROM acquisitionsection WHERE site = ?))");
        //拿到连接
        Connection connection = BaseDBUtils.getConnection();

        //预编译sql语句
        PreparedStatement preparedStatement = BaseDBUtils.getPreparedStatement(connection, sql.toString());

        //存放替换参数
        Object[] parameter=null;

        for (Chapter chapter : list) {
            parameter = new Object[]{chapter.getUuid(),chapter.getSite(),chapter.getName(),chapter.getSite()};
            //返回一个sql的执行状态，是否插入成功
            int i = BaseDBUtils.executeUpdate(preparedStatement,parameter);

            if(i>0){
                map.put(chapter.getName(),"成功");
            } else if (i==0){
                map.put(chapter.getName(),"已存在");
            }else {
                map.put(chapter.getName(),"失败");
            }

        }
        return map;
    }

    /**
     * 单章小说内容入库
     *
     * @param list 小说内容实体
     * @return 存储结果
     */
    @Override
    public Map<String, Object> collectionNovelText(List<NovelText> list) throws SQLException {
        StringBuffer sql = new StringBuffer("INSERT INTO chapters (SELECT null,?,?,null,?)");

        Map<String, Object> map = new HashMap<>();
        //拿到连接
        Connection connection = BaseDBUtils.getConnection();

        //预编译sql语句
        PreparedStatement preparedStatement = BaseDBUtils.getPreparedStatement(connection, sql.toString());

        //存放替换参数
        Object[] parameter=null;

        for (NovelText novelText : list) {
            parameter = new Object[]{novelText.getUuid(),novelText.getName(),novelText.getText()};
            //返回一个sql的执行状态，是否插入成功
            int i = BaseDBUtils.executeUpdate(preparedStatement,parameter);

            if(i>0){
                map.put(novelText.getName(),"成功");
            } else if (i==0){
                map.put(novelText.getName(),"已存在");
            }else {
                map.put(novelText.getName(),"失败");
            }
        }
        return map;

    }

    /**
     * 查询一般所有信息
     *
     * @return
     */
    @Override
    public Map<String, Object> homePageInformationQuery() throws SQLException {
        //存放查询结果
        Map<String, Object> map = new HashMap<String, Object>();
        //拿到连接
        Connection connection = BaseDBUtils.getConnection();
        //基础sql语句
        StringBuffer sql = new StringBuffer("SELECT count(uuid) FROM books WHERE 1=1");
        //查询所有小说语句
        PreparedStatement preparedStatement = BaseDBUtils.getPreparedStatement(connection, sql.toString());
        //所有小说
        ResultSet resultSet = BaseDBUtils.executeQuery(preparedStatement, null);
        int all = 0;
        if(resultSet.next()){
            all = resultSet.getInt(1);
        }
        //完本小说
        String sqlFinish = sql+ " AND state = 0 ";
        PreparedStatement preparedStatement1 = BaseDBUtils.getPreparedStatement(connection, sqlFinish.toString());
        ResultSet resultSet1 = BaseDBUtils.executeQuery(preparedStatement1, null);
        int finish = 0;
        if(resultSet1.next()){
            finish = resultSet1.getInt(1);
        }

        //未完结小说
        int nbspBL = all-finish;

        StringBuffer sqlUser = new StringBuffer("SELECT COUNT(id) FROM `user` WHERE 1=1 ");
        PreparedStatement preparedStatement2 = BaseDBUtils.getPreparedStatement(connection, sqlUser.toString());
        ResultSet resultSet2 = BaseDBUtils.executeQuery(preparedStatement2, null);
        int userNumber = 0;
        if (resultSet2.next()){
            userNumber = resultSet2.getInt("COUNT(id)");
        }
        map.put("all",all);
        map.put("finish",finish);
        map.put("nbspBL",nbspBL);
        map.put("userNumber",  userNumber);
        return map;
    }

    /**
     * 查询小说后台列表
     * @param map 查询筛选条件
     * @return
     * @throws SQLException
     */
    @Override
    public Map<String, Object> novelist(Map<String,Object> map) throws SQLException {
        StringBuffer sql = new StringBuffer("SELECT `name`,author,state,classification,count,intro FROM books WHERE 1=1 LIMIT ?,?");
        //存放查询结果
        Map<String, Object> map1 = new HashMap<String, Object>();

         String page = (String) map.get("page");
        String limit = (String) map.get("limit");
        int i = Integer.parseInt(page);
        int i1 = Integer.parseInt(limit);
        Object[] pr = {(i-1)*i1,i1};
        List<Book> query = queryRunner.query(sql.toString(), pr,new BeanListHandler<Book>(Book.class));
        map1.put("list", query );

        return map1;
    }


}
